﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Agresso.Interface.Layer.Gateway;
using Agresso.Interface.Layer.Domain;
using System.Data.SqlClient;
using Agresso.Interface.Layer.SQL;
using System.Data;
using System.Collections;

namespace Agresso.Interface.Layer.Service
{
    public class GradeAccountMappingService
    {
        DataBaseConnectivity m_dataBaseConectivity;
        AlertService m_alertService;
        public GradeAccountMappingService(DataBaseConnectivity dataBaseConectivity)
        {
            m_dataBaseConectivity = dataBaseConectivity;
            m_alertService = new AlertService();
        }
        public GradeAccountMappingService()
        {
            m_dataBaseConectivity = new DataBaseConnectivity();
            m_alertService = new AlertService();
        }
        public IList GetAllGradeAccountMapping()
        {
            IList result = new ArrayList();
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(GradeAccountMappingSQL.GetAllGradeAccountMapping(), m_dataBaseConectivity.InterfaceSqlConnection);
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    while (rd.Read())
                    {
                        result.Add(new GradeAccountMapping(rd));
                    }
                }
                rd.Close();
                return result;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
            return result;
        }

        //public bool IsValidGradeAccountMapping(string grade)
        //{
        //    try
        //    {
        //        m_dataBaseConectivity.InterfaceSqlConnection.Open();
        //       // m_dataBaseConectivity.SoftmarSqlConnection.Open();
        //        //m_dataBaseConectivity.AgressoSqlConnection.Open();
        //        //find pc in pc mapping table-------------
        //        SqlCommand cmd = new SqlCommand(GradeAccountMappingSQL.GetGradeAccountMappingByName(), m_dataBaseConectivity.InterfaceSqlConnection);
        //        cmd.Parameters.Add("@grade", SqlDbType.NVarChar).Value = grade;
        //        SqlDataReader rd = cmd.ExecuteReader();
        //        GradeAccountMapping chargeType = null;
        //        if (rd.HasRows)
        //        {
        //            rd.Read();
        //            chargeType = new GradeAccountMapping(rd);
        //        }
        //        rd.Close();
        //        //if (chargeType == null)
        //        //{
        //        //    //research all from softmar
        //        //    cmd.Parameters.Clear();
        //        //    cmd.CommandText = GradeAccountMappingSQL.GetAllSoftmarGradeAccount();
        //        //    cmd.Connection = m_dataBaseConectivity.SoftmarSqlConnection;
        //        //    rd = cmd.ExecuteReader();
        //        //    IList chargeAccounts = new ArrayList();
        //        //    if (rd.HasRows)
        //        //    {
        //        //        while (rd.Read())
        //        //        {
        //        //           GradeAccountMapping ct = new GradeAccountMapping(true, rd);
        //        //           chargeAccounts.Add(ct);
        //        //        }
        //        //    }
        //        //    rd.Close();
        //        //    foreach (GradeAccountMapping c in chargeAccounts)
        //        //    {
        //        //        cmd.Parameters.Clear();
        //        //        cmd.CommandText = GradeAccountMappingSQL.GetGradeAccountMappingByName();
        //        //        cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
        //        //        cmd.Parameters.Add("@grade", SqlDbType.NVarChar).Value = c.grade;
        //        //        rd = cmd.ExecuteReader();
        //        //        bool found = rd.HasRows;
        //        //        rd.Close();
        //        //        if (!found)
        //        //        {
        //        //            cmd.Parameters.Clear();
        //        //            cmd.CommandText = GradeAccountMappingSQL.InsertGradeAccountMapping();
        //        //            cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
        //        //            cmd.Parameters.Add("grade", SqlDbType.NVarChar).Value = c.grade;
        //        //            cmd.Parameters.Add("account", SqlDbType.NVarChar).Value = c.account;
        //        //            cmd.Parameters.Add("created_date", SqlDbType.NVarChar).Value = DateTime.Now;
        //        //            cmd.Parameters.Add("modified_date", SqlDbType.NVarChar).Value = DateTime.Now;
        //        //            cmd.ExecuteNonQuery();
        //        //        }
        //        //    }
        //       // }
        //        //if pc did not found in Mapping Table then get from softmar pc master and store in pc mapping table-----------
        //        return chargeType == null ? false : chargeType.account != string.Empty;
        //    }
        //    catch (Exception x)
        //    {
        //        LogService.WriteLogException(x);
        //    }
        //    finally
        //    {
        //        m_dataBaseConectivity.InterfaceSqlConnection.Close();
        //       // m_dataBaseConectivity.SoftmarSqlConnection.Close();
        //    }
        //    return false;
        //}

        public GradeAccountMapping IsValidGradeAccountMapping(string grade, string key, AlertType alertType)
        {
            GradeAccountMapping gradeAccountMapping = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                // m_dataBaseConectivity.SoftmarSqlConnection.Open();
                //m_dataBaseConectivity.AgressoSqlConnection.Open();
                //find pc in pc mapping table-------------
                SqlCommand cmd = new SqlCommand(GradeAccountMappingSQL.GetGradeAccountMappingByName(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@grade", SqlDbType.NVarChar).Value = grade;
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    rd.Read();
                    gradeAccountMapping = new GradeAccountMapping(rd);
                }
                rd.Close();
                if (gradeAccountMapping == null)
                {
                    gradeAccountMapping = new GradeAccountMapping();
                    gradeAccountMapping.grade = grade;

                    cmd.Parameters.Clear();
                    cmd.CommandText = GradeAccountMappingSQL.InsertGradeAccountMapping();
                    cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    cmd.Parameters.Add("grade", SqlDbType.NVarChar).Value = gradeAccountMapping.grade;
                    cmd.Parameters.Add("account", SqlDbType.NVarChar).Value = gradeAccountMapping.account;
                    cmd.Parameters.Add("created_date", SqlDbType.NVarChar).Value = DateTime.Now;
                    cmd.Parameters.Add("modified_date", SqlDbType.NVarChar).Value = DateTime.Now;
                    cmd.ExecuteNonQuery();
                }
                if (gradeAccountMapping.IsValidGradeAccountMapping())
                    m_alertService.CheckAndUpdateAlert(key, alertType, AlertStatus.RESOLVED, "Please update Agresso map for Grade with code : " + grade, "Grade");
                else
                    m_alertService.CheckAndUpdateAlert(key, alertType, AlertStatus.PENDING, "Please update Agresso map for Grade with code : " + grade, "Grade");
                    //if pc did not found in Mapping Table then get from softmar pc master and store in pc mapping table-----------
                return gradeAccountMapping  ;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                // m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
            return gradeAccountMapping;
        }
        public GradeAccountMapping GetGradeAccountMapping(string grade)
        {
            GradeAccountMapping gradeAccountMapping = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(GradeAccountMappingSQL.GetGradeAccountMappingByName(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@grade", SqlDbType.NVarChar).Value = grade;
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    rd.Read();
                    gradeAccountMapping = new GradeAccountMapping(rd);
                }
                rd.Close();
                if (gradeAccountMapping == null)
                {
                    gradeAccountMapping = new GradeAccountMapping();
                    gradeAccountMapping.grade = grade;
                    cmd.Parameters.Clear();
                    cmd.CommandText = GradeAccountMappingSQL.InsertGradeAccountMapping();
                    cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                    cmd.Parameters.Add("grade", SqlDbType.NVarChar).Value = gradeAccountMapping.grade;
                    cmd.Parameters.Add("account", SqlDbType.NVarChar).Value = gradeAccountMapping.account;
                    cmd.Parameters.Add("account_grn", SqlDbType.NVarChar).Value = gradeAccountMapping.account_grn;
                    cmd.Parameters.Add("created_date", SqlDbType.NVarChar).Value = DateTime.Now;
                    cmd.Parameters.Add("modified_date", SqlDbType.NVarChar).Value = DateTime.Now;
                    cmd.ExecuteNonQuery();
                }
                return gradeAccountMapping;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                // m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
            return gradeAccountMapping;
        }
        public GradeAccountMapping GetGradeAccountMappingByAccount(string account)
        {
            GradeAccountMapping gradeAccountMapping = null;
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand(GradeAccountMappingSQL.GetGradeAccountMappingByAccount(), m_dataBaseConectivity.InterfaceSqlConnection);
                cmd.Parameters.Add("@account", SqlDbType.NVarChar).Value = account;
                SqlDataReader rd = cmd.ExecuteReader();
                if (rd.HasRows)
                {
                    rd.Read();
                    gradeAccountMapping = new GradeAccountMapping(rd);
                }
                rd.Close();
                return gradeAccountMapping;
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
                throw x;
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
                // m_dataBaseConectivity.SoftmarSqlConnection.Close();
            }
            return gradeAccountMapping;
        }

        public void UpdateGradeAccountMapping(GradeAccountMapping chargeType)
        {
            try
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = GradeAccountMappingSQL.UpdateGradeAccountMapping();
                cmd.Connection = m_dataBaseConectivity.InterfaceSqlConnection;
                cmd.Parameters.Clear();
                cmd.Parameters.Add("@gam_id", SqlDbType.Int).Value = chargeType.gam_id;
                cmd.Parameters.Add("@grade", SqlDbType.NVarChar).Value = chargeType.grade;
                cmd.Parameters.Add("@account", SqlDbType.NVarChar).Value = chargeType.account;
                cmd.Parameters.Add("@account_grn", SqlDbType.NVarChar).Value = chargeType.account_grn;
                cmd.Parameters.Add("@modified_date", SqlDbType.NVarChar).Value = DateTime.Now;
                cmd.ExecuteNonQuery();
            }
            catch (Exception x)
            {
                LogService.WriteLogException(x);
            }
            finally
            {
                m_dataBaseConectivity.InterfaceSqlConnection.Close();
            }
        }
    }
}
